using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Collections;
using System.Text;
using Norm.Attribute;
using Norm.Generic;
using Collections = System.Collections.Generic;

namespace Norm.Helper
{
    /// <summary>
    /// Convert Sql to Objects
    /// </summary>
    public sealed class SqlHelper
    {
        /// <summary>
        /// Reads the current row from the reader object and 
        /// assigns fields with columns matching their names
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="o"></param>
        public static object Convert(SqlDataReader reader, object o)
        {
            if (!reader.HasRows)
                throw new Exception.UnableToRead();
            
            FieldInfo[] fields = DatabaseFields(o);//o.GetType().GetFields();
            string tableName = TableName(o);
            foreach (FieldInfo fi in fields)
            {
                if (DatabaseField(fi).IsComplexObject)
                {
                    fi.SetValue(o, Convert(reader, fi.GetValue(o)));
                    continue;
                }

                object val;
                try
                {
                    val = reader[string.Format(
                            "{0}_{1}",
                                tableName,
                                fi.Name)];
                    fi.SetValue(o, val);
                }
                catch { continue; }
            }
            return o;
        }

        public static FieldInfo[] DatabaseFields(object o)
        { return DatabaseFields(o.GetType()); }

        public static FieldInfo[] DatabaseFields(Type t)
        {            
            List<FieldInfo> list = new List<FieldInfo>();
            foreach (FieldInfo fi in t.GetFields())
                if (ObjHelper.FieldAttributes(fi, typeof(Attribute.DatabaseField)).Length != 0)
                    list.Add(fi);
            return list.ObjArray();
        }

        public static Attribute.DatabaseField DatabaseField(FieldInfo field)
        {
            Attribute.DatabaseField[] atts = (Attribute.DatabaseField[])Helper.ObjHelper.FieldAttributes(field, typeof(Attribute.DatabaseField));
            if (atts != null && atts.Length > 0)
                return atts[0];
            return null;
        }

        public static string TableName(Type t)
        {
            return TableName(ObjHelper.NewObj(t));
        }
        
        public static string TableName(object o)
        {
            string name = o.GetType().Name;
            foreach (object att in ObjHelper.ClassAttributes(o, typeof(Attribute.Table)))
                if (att != null &&
                    !string.IsNullOrEmpty(((Attribute.Table)att).TableName))
                    name = ((Attribute.Table)att).TableName;
            return name;
        }

        public static void ValidateReader(SqlDataReader reader)
        {
            if (reader == null) throw new Exception.UnableToRead();
            if (!reader.HasRows) throw new Exception.UnableToRead();
        }

        public static FieldInfo PrimaryKeyField(Type t)
        {
            return PrimaryKeyField(Helper.ObjHelper.NewObj(t));
        }
        public static FieldInfo PrimaryKeyField(object o)
        {
            FieldInfo[] fis = Helper.SqlHelper.DatabaseFields(o);
            foreach (FieldInfo fi in fis)
            {
                DatabaseField dbf = SqlHelper.DatabaseField(fi);
                if (dbf.IsPrimaryKey)
                    return fi;
            }
            throw new Exception.NoKeys();
        }

        /// <summary>
        /// FIXME: IsEnum Logic partially implemented 
        /// </summary>
        /// <param name="o"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public static object CreateObject(object o, SqlConnection c)
        {
            //Create all complex objects below us
            foreach(FieldInfo fi in ComplexFields(o))
            {
                //If fobj is EnumTable dont recurse
                bool isEnum = false;
                if (DatabaseField(fi).IsComplexObject)
                {
                    Table[] atts = (Table[])ObjHelper.ClassAttributes(fi.GetValue(o), typeof(Attribute.Table));
                    if (atts != null && atts.Length > 0)
                        foreach (Table att in atts)
                            if (att.IsEnum)
                            { isEnum = true; break; }
                }

                //other logic might be needed here later to handle other
                //"unique" flags on objs

                //if we arent an enum create the object
                if (!isEnum)
                {
                    object oc = CreateObject(fi.GetValue(o), c);
                    fi.SetValue(o, oc);
                }
            }

            object val = Create(o, c).ExecuteScalar();
            PrimaryKeyField(o).SetValue(o, val);
            return o;            
        }

        public static FieldInfo[] UniqueColumns(object o)
        {
            StringBuilder sb = new StringBuilder();
            string tableName = TableName(o);
            ArrayList al = new ArrayList();
            foreach (FieldInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).IsUniqueColumn)
                    al.Add(fi);

            FieldInfo[] fis = new FieldInfo[al.Count];
            for (int i = 0; i < al.Count; i++)
                fis[i] = (FieldInfo)al[i];
            return fis;
        }

        /// <summary>
        /// Creates the query needed to insert the object into the db (does not support complex yet)
        /// </summary>
        /// <param name="o"></param>
        /// <param name="cmd">ref to the SqlCommand where params will be added as the cmd is built</param>
        /// <returns></returns>
        public static string InsertObjectQuery(object o, ref SqlCommand cmd)
        {
            //Make sure the guid is set
            FieldInfo boidField = o.GetType().GetField("BaseObjectId");
            if (boidField != null && Guid.Equals(boidField.GetValue(o), Guid.Empty))
                boidField.SetValue(o, Guid.NewGuid());

            string tableName = TableName(o);
            StringBuilder sb = new StringBuilder(string.Format
                ("declare @{0} int;",                                         
                    PrimaryKeyField(o).Name));
            
            FieldInfo[] uniqueCols = UniqueColumns(o);
            if(uniqueCols.Length > 0)
            {
                //Declare a var to hold out counts
                sb.AppendFormat("declare @{0}_count int;", tableName);
                
                //Select the total of time our unique criteria occurs
                sb.AppendFormat("select @{0}_count = count(*) from [{0}] where ", tableName);
                foreach (FieldInfo fi in uniqueCols)
                {
                    string varName = string.Format("@unique_{0}_{1}", tableName, fi.Name);
                    sb.AppendFormat("[{0}] = {1} and", fi.Name, varName);
                    cmd.Parameters.AddWithValue(varName, fi.GetValue(o));
                }
                sb = new StringBuilder(sb.ToString().TrimEnd(" and".ToCharArray())).Append(";");

                //If the count is > 0 
                sb.AppendFormat("if @{0}_count > 0 begin\n", tableName);
                
                //Then there is no reason to insert the record
                //Just select it
                sb.Append(string.Format
                    ("select top 1 @{0} = [{0}] from [{1}] where ", PrimaryKeyField(o).Name,tableName));                    
                foreach (FieldInfo fi in uniqueCols)
                {
                    string varName = string.Format("@unique_{0}_{1}", tableName, fi.Name);
                    sb.AppendFormat("[{0}] = {1} and", fi.Name, varName);                    
                }
                sb = new StringBuilder(sb.ToString().TrimEnd(" and".ToCharArray())).Append(";");
                sb.AppendFormat("select cast(@{0} as int);", PrimaryKeyField(o).Name);

                //else we need to insert
                sb.Append("end else begin ");
            }

            sb.Append(string.Format
                ("insert into [{0}] (", 
                    tableName));
            
            //Append column list for all non pkeys
            foreach (FieldInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).Stored)
                    sb.Append(string.Format
                        ("[{0}],", fi.Name));
                else if (DatabaseField(fi).IsComplexObject)
                    sb.Append(string.Format
                        ("[{0}],", ForeignKey(o,fi).Name));
            
            //Remove extra trailing comma
            sb = new StringBuilder(sb.ToString().TrimEnd(",".ToCharArray()));
         
            //Begin inserting of values
            sb.Append(" ) values (");

            //Append each var in order
            foreach (FieldInfo fi in DatabaseFields(o))
            {
                DatabaseField dbf = DatabaseField(fi);

                //Dont insert pkey, this will fail as identity should be enabled
                if (dbf.IsPrimaryKey)
                    continue;

                //Figure out what param to call this
                //If it is a complex object we will use the @ForiegnKey
                //otherwise the variable will be called TableName_ColumnName
                //Note: ForeignKey must be defined in this query
                string paramName;
                if (dbf.IsComplexObject)
                    paramName = string.Format
                        ("@{0}", ForeignKey(o,fi).Name);
                else
                    paramName = string.Format
                        ("@{0}_{1}", tableName, fi.Name);
                
                sb.Append(string.Format("{0},", paramName));

                //If not a complex object add the var otherwise it should have been added when the object is created
                if(!dbf.IsComplexObject)
                    cmd.Parameters.AddWithValue(paramName, fi.GetValue(o));
            }
            sb = new StringBuilder(sb.ToString().TrimEnd(",".ToCharArray())).Append(");");

            //set the pkey in the query
            //If there are unique columns then we dont need to do the selecting of the identity
            //  it would be an extra step and would contain random data
            if (uniqueCols.Length == 0)
                sb.Append(string.Format
                    ("set @{0} = cast(@@IDENTITY as int);", PrimaryKeyField(o).Name));
            else
                sb.Append(" end;");

            return sb.ToString();            
        }

        public static SqlCommand Create(object o)
        {
            if (o == null)
                throw new Exception.BlankObject();

            string tableName = TableName(o);
            SqlCommand cmd = new SqlCommand();
            StringBuilder sb = new StringBuilder();

            foreach (FieldInfo fi in ComplexFields(o))
            {
                //Dont recurse into enum tables
                bool shouldCreate = true;
                object fo = fi.GetValue(o);
                foreach (Attribute.Table att in ObjHelper.ClassAttributes(fo, typeof(Attribute.Table)))
                    if (att.IsEnum)
                    {
                        cmd.Parameters.AddWithValue(string.Format(
                            "@{0}",
                            PrimaryKeyField(fo).Name), 
                            PrimaryKeyField(fo).GetValue(fo));
                        shouldCreate = false; 
                        break; 
                    }

                if(shouldCreate)
                    sb.Append(InsertObjectQuery(fi.GetValue(o), ref cmd));
            }
            sb.Append(InsertObjectQuery(o, ref cmd));

            cmd.CommandText = sb.Append(";select cast(@@IDENTITY as int)").ToString();
            return cmd;
        }

        public static bool IsStoredField(string field, object o)
        {
            object [] atts = Helper.ObjHelper.FieldAttributes(field,o,typeof(Attribute.DatabaseField));
            
            if (atts == null || atts.Length == 0)
                return false;

            if (((Attribute.DatabaseField)atts[0]).Stored)
                return true;
            return false;
        }

        public static SqlCommand Create(object o, SqlConnection c)
        {
            SqlCommand cmd = Create(o);
            cmd.Connection = c;
            return cmd;
        }

        //Convert the C# type to DB Type
        public static string DatabaseType(FieldInfo fi)
        {
            //Get the type name
            string type = fi.FieldType.ToString();
            switch (type)
            {
                case "System.String":return "varchar(MAX)";
                case "System.Int32": return "int";
                case "System.Double": return "double";
                case "System.DateTime": return "datetime";
                case "System.Guid": return "uniqueidentifier";
                case "System.Boolean": return "bit";
                default: throw new Exception.NotValidEnumValue();
            }
        }

        /// <summary>
        /// Get the query that would be needed to create the table to store the obj
        /// </summary>
        /// <param name="o"></param>
        /// <returns>query text</returns>
        public static string CreateTableQuery(object o)
        {
            //Make sure there are fields to this table
            //Otherwise just throw NoKeys exception
            FieldInfo[] fields = SqlHelper.DatabaseFields(o);
            if (fields.Length == 0)
                throw new Exception.NoKeys();

            //Get the table name and begin constructing the query
            string tableName = SqlHelper.TableName(o);
            StringBuilder sb = new StringBuilder(
                string.Format("create table [{0}] ( ", tableName));

            foreach (FieldInfo fi in fields)
            {
                DatabaseField dbf = SqlHelper.DatabaseField(fi);

                //If the field isnt stored and is not a primary key skip                
                if (!dbf.Stored && !dbf.IsPrimaryKey && !dbf.IsComplexObject)
                    continue;

                //Declare var
                string colType;
                string colName;
                
                //If this is not a complex object do normal actions
                if (!dbf.IsComplexObject)
                {
                    colType = DatabaseType(fi);
                    colName = fi.Name;
                }
                else //create column as a foreign key
                {
                    if (fi.FieldType.IsArray)
                        continue;

                    FieldInfo fkey =ForeignKey(o, fi);
                    colName = fkey.Name;
                    colType = DatabaseType(fkey); 
                }

                sb.Append(string.Format("[{0}] {1} ", colName,colType ));

                //Mark primary key if needed
                if (dbf.IsPrimaryKey)
                    if(colType.Equals("uniqueidentifier"))
                        sb.Append("primary key RowGuidcol ");
                    else          
                        sb.Append("primary key identity(1,1) ");

                //Append ,
                sb.Append(", ");
            }

            //Remove the trailing ', ' from the last field
            sb = new StringBuilder(sb.ToString().TrimEnd(", ".ToCharArray())).Append(");");

            //Add unique indexes to the table
            foreach (FieldInfo fi in UniqueColumns(o))
                sb.AppendFormat("create unique index UQ_{0}_{1} on [{0}]([{1}]);", tableName, fi.Name);

         
            //Append array tables if needed
            sb.Append(CreateArrayTables(o));
   
            return sb.ToString();
        }

        //Create a table and return the success code
        public static int CreateTable(object o, SqlConnection c)
        {
            using (SqlCommand cm = new SqlCommand(CreateTableQuery(o), c))
            {
                cm.CommandType = CommandType.Text;
                return cm.ExecuteNonQuery();
            }
        }

        public static string ArrayTableName(object o, FieldInfo fi)
        {            
            return string.Format
                ("mt_{0}_{1}_{2}",
                new object[] { 
                    TableName(o), 
                    TableName(ObjHelper.InstanceOfArrayType(fi)), 
                    fi.Name });
        }

        /// <summary>
        /// Creates the query to add middle tables to store arrays
        /// Note: The blog outputs a check if the object exists if so nothing will happen (no drops)
        /// </summary>
        /// <param name="o"></param>
        /// <returns>query text</returns>
        public static string CreateArrayTables(object o)
        {
            StringBuilder sb = new StringBuilder();
            foreach (FieldInfo fi in DatabaseFields(o))
            {
                if (!fi.FieldType.IsArray)
                    continue;

                object fo = fi.GetValue(o);
                if (fo == null)
                {
                    //HACK: Turn 'fish.obj[]' into 'fish.obj'
                    string typeName =fi.FieldType.FullName.TrimEnd("[]".ToCharArray());
                    fo = fi.FieldType.Assembly.CreateInstance(typeName);
                }
                
                string ftable = TableName(fo);
                string middleTable = ArrayTableName(o, fi);

                sb.AppendFormat("if object_id(N'{0}' is null begin ", middleTable);
                sb.AppendFormat
                    (@"create table [{0}] 
                        ( [{0}Id] int primary key identity(1,1),
                            [{1}_BaseObjectId] uniqueidentifier not null,
                            [{2}_BaseObjectId] uniqueidentifier not null);",
                    new object[] { middleTable, TableName(o), ftable });
                sb.Append("end;");
            }
            return sb.ToString();
        }

        public static string SelectCols(object o)
        {
            StringBuilder sb = new StringBuilder();
            string tableName = TableName(o);

            foreach (FieldInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).IsComplexObject)
                    sb.Append(SelectCols(fi.GetValue(o))).Append(",");
                else
                    sb.Append(string.Format
                        ("[{0}].[{1}] as [{0}_{1}],", tableName, fi.Name));

            return sb.ToString().Trim(",".ToCharArray());
        }

        public static object RetrieveObject(object o, object pk, SqlConnection c)
        {
            string query = string.Format(
                @"select top 1 {0} from [{1}] where [{2}] = @pkey",
                    new object[]{
                    SelectCols(o),
                    SqlHelper.TableName(o),
                    PrimaryKeyField(o).Name});

            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@pkey", pk);
                
                using(SqlDataReader reader = cmd.ExecuteReader())
                {
                    if(reader.Read())
                        return SqlHelper.Convert(reader, o);
                }
             
                throw new Exception.BlankObject();
            }
        }

        /// FIXME: Needs to support Complex objects
        public static int UpdateObject(object o, SqlConnection c)
        {
            //Blank or null pk dont continue as this could be REALLY bad for our system
            if (string.IsNullOrEmpty(System.Convert.ToString(PrimaryKeyField(o).GetValue(o))))
                throw new Exception.BlankObject();

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = c;
                cmd.CommandType = CommandType.Text;

                //build the query
                string query = string.Format("update [{0}] set ", TableName(o));
                
                //add params to update
                foreach (FieldInfo fi in DatabaseFields(o))
                {
                    //Dont update the pk, the update will always fail
                    DatabaseField dbf = DatabaseField(fi);
                    if (dbf.IsPrimaryKey)
                        continue;

                    //Complex objects just recurse into
                    if (dbf.IsComplexObject)
                    {
                        object fo = fi.GetValue(o);
                        FieldInfo fkey = Helper.SqlHelper.ForeignKey(o,fi);    
                        
                        //small optimization
                        //if the for obj is an Enum table dont update it
                        //to update enums pass the enum in explicitly
                        object[] atts = ObjHelper.ClassAttributes(fo, typeof(Attribute.Table));
                        bool Recurse = true;
                        if (atts != null)
                            foreach (object att in atts)
                                if (((Attribute.Table)att).IsEnum)
                                    Recurse = false;
                        if(Recurse)
                            UpdateObject(fo, c);

                        query += string.Format
                            ("[{0}]=@{0}, ", fkey.Name);

                        cmd.Parameters.AddWithValue
                            ("@" + fkey.Name, fkey.GetValue(fo));
                    }
                    else
                    {
                        query += string.Format("[{0}]=@{0}, ", fi.Name);
                        cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(o));
                    }
                }

                //remove trailing comma
                query = query.TrimEnd(", ".ToCharArray());

                //set the where clause
                query += string.Format(" where [{0}] = @pkey", PrimaryKeyField(o).Name);
                cmd.Parameters.AddWithValue("@pkey", PrimaryKeyField(o).GetValue(o));

                //set the cmd to use query
                cmd.CommandText = query;

                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// FIXME: Does not support complex objects
        /// </summary>
        /// <param name="o"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public static int DeleteObject(object o, SqlConnection c)
        {
            string query = string.Format("delete from [{0}] where [{1}] = @pkey",
                TableName(o), PrimaryKeyField(o));

            using (SqlCommand cmd = new SqlCommand(query,c))
            {
                cmd.Parameters.AddWithValue("@pkey", PrimaryKeyField(o).GetValue(o));
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static FieldInfo[] ComplexFields(object o)
        {
            Generic.List<FieldInfo> fis = new Norm.Generic.List<FieldInfo>();
            foreach(FieldInfo fi in DatabaseFields(o))
                if(DatabaseField(fi).IsComplexObject)
                    fis.Add(fi);
            return fis.ObjArray();
        }

        public static FieldInfo[] SearchableTextFields(Type t)
        { return SearchableTextFields(Helper.ObjHelper.NewObj(t)); }
        public static FieldInfo[] SearchableTextFields(object o)
        {
            Generic.List<FieldInfo> fs = new Norm.Generic.List<FieldInfo>();
            foreach (FieldInfo fi in DatabaseFields(o))
                if (DatabaseField(fi).IsSearchableText)
                    fs.Add(fi);
            return fs.ObjArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="o">Parent Object</param>
        /// <param name="fi"></param>
        /// <returns></returns>
        public static FieldInfo ForeignKey(object o, FieldInfo fi)
        {
            return PrimaryKeyField(fi.GetValue(o));
        }

        public static string RetrieveComplexQuery(object o)
        {
            if (o == null)
                throw new Norm.Exception.BlankObject();
            //Construct query
            string query = string.Format("select {0} from [{1}] ", SelectCols(o), TableName(o));

            //Assemble inner joins
            string pTable = TableName(o);
            foreach (FieldInfo fi in ComplexFields(o))
            {
                string fTable = TableName(
                    Activator.CreateInstance(fi.GetValue(o).GetType()));
                string fkey = ForeignKey(o, fi).Name;

                query += string.Format(" inner join [{0}] on [{1}].[{2}] = [{0}].[{2}] ",
                    new object[] { fTable, pTable, fkey });
            }
            return query;
        }

        /// <summary>
        /// Returns the join part of query to retrieve the complex object
        /// </summary>
        /// <param name="o"></param>
        /// <returns></returns>
        public static string FromObjectQuery(Type t)
        {
            return FromObjectQuery(ObjHelper.NewObj(t));
        }
        public static string FromObjectQuery(object o)
        {
            //Assemble inner joins
            string pTable = TableName(o);
            string query = TableName(o);
            foreach (FieldInfo fi in ComplexFields(o))
            {
                string fTable = TableName(
                    Activator.CreateInstance(fi.GetValue(o).GetType()));
                string fkey = ForeignKey(o, fi).Name;

                query += string.Format(" inner join [{0}] on [{1}].[{2}] = [{0}].[{2}] ",
                    new object[] { fTable, pTable, fkey });
            }
            return query;
        }

        public static object ReaderToComplexObject(object o, SqlDataReader reader)
        {
            //Nothing back nothing to do
            if (!reader.HasRows) throw new Exception.BlankObject();
            if (!reader.Read()) throw new Exception.BlankObject();

            foreach (FieldInfo fi in ComplexFields(o))
            {
                //Get the complexObject from the base
                object co = Convert(reader, fi.GetValue(o));

                //Set the complexObj now that it has been created/converted
                fi.SetValue(o, co);
            }

            //Set up base obj
            o = Convert(reader, o);

            return o;
        }

        public static object RetrieveComplexObject(object o, object pk, SqlConnection c)
        {
            //Construct query
            string query = RetrieveComplexQuery(o);

            //Add where clause
            query += string.Format(" where [{0}] = @pkey ", PrimaryKeyField(o).Name);

            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                //Setup command
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@pkey", pk);

                SqlDataReader reader = cmd.ExecuteReader();

                o = ReaderToComplexObject(o, reader);
                
                //close reader
                reader.Close();
                return o;
            }
        }

        /// <summary>
        /// Retrieves a simple object by BaseObjectId
        /// </summary>
        /// <param name="o"></param>
        /// <param name="boid"></param>
        /// <param name="c"></param>
        /// <returns></returns>
        public static object RetrieveByBaseObjectId(object o, object boid, SqlConnection c)
        {
            string query = string.Format
                ("{0} where [{1}].[BaseObjectId] = @BaseObjectId",
            RetrieveComplexQuery(o),
            Helper.SqlHelper.TableName(o));
            
            using (SqlCommand cmd = new SqlCommand(query, c))
            {
                cmd.Parameters.AddWithValue("@BaseObjectId", boid);
                SqlDataReader reader = cmd.ExecuteReader();
                //if (reader.Read())
                //    o = Helper.SqlHelper.Convert(reader, o);
                o = ReaderToComplexObject(o, reader);
                reader.Close();
            }
            return o;
        }
        public static string AppendOrderBy(Norm.Generic.List<string> orderby, string query)
        {
            if (orderby.Count > 0)
            {
                query += " order by [";
                query += string.Join("],[", orderby.ObjArray());
                query = query.TrimEnd("[".ToCharArray());
            }

            return query;
        }

        /// <summary>
        /// Append a where clause to a querystring
        /// </summary>
        /// <remarks>WhereValue's are treated special and are used to do other compares</remarks>
        /// <param name="where">where[table][col]=value</param>
        /// <param name="query"></param>
        /// <returns></returns>
        public static SqlCommand AppendWhere(Collections.Dictionary<string, Collections.Dictionary<string, object>> where, ref string query)
        {
            //Start the command to be returned
            SqlCommand cmd = new SqlCommand(query);

            //Nothing in, just return
            if (where.Count > 0)
                query += "where ";
            else
                return cmd;

            //Var index so no prob with overlapping names
            int index = 0;

            //Foreach column in each table
            foreach (string table in where.Keys)
                foreach (string col in where[table].Keys)
                {
                    //Name our var something unique
                    string varName = string.Format
                        ("@w__{0}", index++);

                    //The defaults are:
                    //  operator is equal, and value is the current cell value
                    string op = "=";
                    object value = where[table][col];

                    //If the value's type is a WhereValue
                    if (value.GetType().Equals(typeof(Norm.WhereValue)))
                    {
                        //Override the op and set the real value
                        WhereValue wv = (WhereValue)value;
                        op = wv.Operator;
                        value = wv.Value;
                    }

                    //Append [table].[name] [=] @w__1 and
                    query += string.Format
                        ("[{0}].[{1}] {2} {3} and ",
                            new object[]{ 
                                table,
                                col,
                                op,
                                varName});

                    //Append the value to the cmd
                    cmd.Parameters.AddWithValue(varName, value);
                }

            //Trim any left over "and"s
            query = query.TrimEnd("and ".ToCharArray());

            //Set the cmd and return
            cmd.CommandText = query;
            return cmd;
        }

    }
}